#!/usr/bin/env python
# coding:utf-8


import sys
from copy import deepcopy
from config.config import configs
from datatabletransfer import DataTableTransfer
from utils.commontools import convert_to_date
from utils.mysqlutils import MysqlUtils
from fieldsmap import *
import logging


class PeopleBasicInfo(DataTableTransfer):
    """
    个人档案(TQ_COMP_PERSONRECORD)(基金经理) --> 人物信息表(peo_basic_info)
    """

    # 待插入的数据查询SQL
    QUERY_SOURCE = ('''select a.PERSONALCODE as source_id from TQ_COMP_PERSONRECORD a 
    where a.PERSONALCODE in (select b.MANAGERCODE from TQ_FD_MGPERFORMANCE b)
    union all
    select a.PERSONALCODE as source_id from TQ_COMP_PERSONRECORD a 
    where a.PERSONALCODE in (select b.PSCODE from TQ_FD_COMPMGINFO b) 
    union all
    select a.PERSONALCODE as source_id from TQ_COMP_PERSONRECORD a 
    where a.PERSONALCODE in (select b.SHHOLDCODE from TQ_FD_COMPOWNER b);''')

    QUERY_TARGET = ('''select replace(source_id, 'FD', '') as source_id from peo_basic_info where come_source = 'zh-data' and status<>'9';;''')

    TABLE = 'peo_basic_info'  # 待插入的表名
    SOURCE_ID = {'source_id': False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'peo_uni_code'  # 更新主键

    MAP_FIELDS = PeopleBasicInfoMap
    MAP_VALUES = PeopleBasicInfoValueMap


class FundManagerBasicInfo(DataTableTransfer):
    """
    基金经理经营业绩表(TQ_FD_MGPERFORMANCE) --> 基金经理基本信息(fund_manager_basic_info)
    """
    QUERY_SOURCE=('''select a.SECURITYID as  sec_uni_code ,a.MANAGERNAME as fund_manager_name,a.BEGINDATE as begin_date
    from TQ_FD_MGPERFORMANCE a left join TQ_COMP_PERSONRECORD b on a.MANAGERCODE=b.PERSONALCODE ;''')

    QUERY_TARGET = ( '''select replace(b.source_id,'FD','') as sec_uni_code,fund_manager_name ,replace(begin_date,'-','') as begin_date
    from fund_manager_basic_info p join sec_basic_info b on p.sec_uni_code = b.sec_uni_code   where b.sec_type=1004003 and 
    b.sec_small_type in(1004003002,1004003003) and p.status<>'9';''')

    TABLE = 'fund_manager_basic_info'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'fund_manager_name':False, 'begin_date':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键

    DATETIME = {
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundManagerBasicInfoMap
    MAP_VALUES = FundManagerBasicInfoValueMap


class InstitutionsChange(DataTableTransfer):
    """
    公司概况变动表(TQ_COMP_INFOCHG) --> 机构信息变动表(org_basic_info_chg)
    """
    QUERY_SOURCE = '''select COMPCODE as org_uni_code, CHGTYPE as change_type,BEGINDATE as begin_date
    from TQ_COMP_INFOCHG;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as org_uni_code,change_type,replace(begin_date,'-','') as begin_date
     from org_basic_info_chg c  join org_basic_info b on c.org_uni_code = b.com_uni_code where c.status!='9' and c.come_source='zh-data';'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', COMPCODE) as COMPCODE, PUBLISHDATE,CHGTYPE,BEGINDATE,ENDDATE,BECHG,AFCHG,
    # CHGEXP, 'zh-data' as COME_SOURCE from TQ_COMP_INFOCHG limit %s, 1000;''')

    TABLE = 'org_basic_info_chg'  # 待插入的表名
    SOURCE_ID = {'org_uni_code':False, 'change_type':True, 'begin_date':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键

    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None
    }
    MAP_FIELDS = InstitutionsChangeMap
    MAP_VALUES = InstitutionsChangeValueMap


class SecuritiesChange(DataTableTransfer):
    """
    基金基本资料变动(TQ_FD_BASICINFOCHG) --> 证券基本信息变动表(sec_basic_info_chg)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,CHANGETYPE as change_type,BEGINDATE as begin_date
     from TQ_FD_BASICINFOCHG ;'''
    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,change_type ,replace(begin_date,'-','') as begin_date
    from sec_basic_info_chg c join sec_basic_info b on c.sec_uni_code = b.sec_uni_code where  c.status<>'9' and c.come_source='zh-data';'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID,PUBLISHDATE,CHANGETYPE,BEGINDATE,ENDDATE,BECHG,AFCHG,
    # 'zh-data' as COME_SOURCE from TQ_FD_BASICINFOCHG limit %s, 1000;''')

    TABLE = 'sec_basic_info_chg'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'change_type':True, 'begin_date':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键

    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None
    }

    MAP_FIELDS = SecuritiesChangeMap
    MAP_VALUES = SecuritiesChangeValueMap


class FundComHolder(DataTableTransfer):
    """
    基金公司股东(TQ_FD_COMPOWNER) --> 基金公司股东(fund_com_holder)
    """
    QUERY_SOURCE = '''select  COMPCODE as org_uni_code,BEGINDATE as begin_date,SHHOLDCODE as source_id
        from TQ_FD_COMPOWNER ;'''
    QUERY_TARGET = '''select replace(b.source_id,'FD','') as org_uni_code ,replace(begin_date,'-','') as begin_date,
    replace(f.source_id,'FD','') as source_id from fund_com_holder f join org_basic_info b on f.org_uni_code = b.com_uni_code 
    where  f.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',COMPCODE) as COMPCODE,PUBLISHDATE,BEGINDATE,ENDDATE,
    # concat('FD',SHHOLDCODE) as SHHOLDCODE,SHHOLDNAME,HOLDQUT,HOLDRTO,HOLDERRANK
    # from TQ_FD_COMPOWNER limit %s, 1000;''')

    TABLE = 'fund_com_holder'  # 待插入的表名
    SOURCE_ID = {'org_uni_code':False, 'begin_date':False, 'source_id':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键

    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None
    }

    MAP_FIELDS = FundComHolderMap
    MAP_VALUES = FundComHolderValueMap

    @classmethod
    def upset(cls, table, row, column_value, upset):
        """
        更新记录: shareholder_code, shareholder_type取值范围
        1.shareholder_code字段值来自机构信息表(org_basic_info)则shareholder_type='1';
        2.shareholder_code字段值来自人物信息表(peo_basic_info)则shareholder_type='2';
        3.shareholder_code字段值不属于以上两种情况，则shareholder_type=NULL
        :param table:
        :param row:
        :param column_value:
        :param upset:
        :return:
        """

        item = deepcopy(cls.MAP_FIELDS)
        for key, value in item.iteritems():
            # 判断是否存在值匹配
            if value and cls.MAP_VALUES.has_key(key):
                map_value = cls.MAP_VALUES[key]
                if map_value.keys()[0] == 'SQL':   # 从其他表获取数据
                    conf = map_value.values()[0][0]
                    query = map_value.values()[0][1]
                    default_value = map_value.values()[0][2]
                    item[key] = cls.exec_sql(conf, query, (row[value], ), default_value)
                else:
                    item[key] = map_value.get(row[value], 0)
            # 如果value存在，进行匹配
            elif value:
                item[key] = row[value] if row[value] or row[value] == 0 else None

        # 修正默认值
        for key, value in cls.DEFAULT_VALUE.iteritems():
            if not item[key]:
                item[key] = value

        # 修正日期时间型
        for key, value in cls.DATETIME.iteritems():
            if item.has_key(key) and len(str(item[key])) == 8 and item[key] != '19000101':
                item[key] = convert_to_date('{0}-{1}-{2} 00:00:00'
                                            .format(item[key][:4], item[key][4:6], item[key][6:8]))
            else:
                item[key] = None

        # shareholder_code, shareholder_type取值
        item['shareholder_code'], item['shareholder_type'] = cls.get_shareholder_code_type(row['SHHOLDCODE'])
        cls.write_mysql(table, item, column_value, upset)

    @classmethod
    def get_shareholder_code_type(cls, sh_holder_code):
        """
        处理shareholder_code, shareholder_type取值
        :param sh_holder_code:
        :return:
        """

        shareholder_code = None
        shareholder_type = None
        org_query = '''select com_uni_code from org_basic_info where source_id=%s and status!='9' limit 1;'''
        peo_query = '''select peo_uni_code from peo_basic_info where source_id=%s and status!='9' limit 1;'''
        org_uni_code = cls.exec_sql(conf='data_center', sql=org_query, params=(sh_holder_code, ))

        if org_uni_code:
            shareholder_code = org_uni_code
            shareholder_type = '1'
        else:
            peo_uni_code = cls.exec_sql(conf='data_center', sql=peo_query, params=(sh_holder_code, ))
            if peo_uni_code:
                shareholder_code = peo_uni_code
                shareholder_type = '2'
        return shareholder_code, shareholder_type


class FundCode(DataTableTransfer):
    """
    证券代码对应表(TQ_OA_SECURITYMAP) --> 基金编码表(fund_code)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code, BEGINDATE as begin_date,
     MAPTYPE as map_type ,MAPCODE as map_code from TQ_OA_SECURITYMAP where MAPTYPE in 
    (5,14,16,17,18,19,22,24,25,40,47,48,50,61,64,65,68,69,7,8,9) and SETYPE IN ('301','302');'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(begin_date,'-','') as begin_date,map_type,map_code
    from fund_code f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code  where  f.status<>'9'  and b.sec_type=1004003 and 
    b.sec_small_type in(1004003002,1004003003);'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', SECURITYID) SECURITYID, MAPTYPE, MAPCODE,
    # case when MAPTYPE=15 and EXCHANGE='001002' then concat(MAPCODE, '.SH')
    # when MAPTYPE=15 and EXCHANGE='001003' then concat(MAPCODE, '.SZ') else '' end as ABC_CODE, MAPNAME, BEGINDATE,
    # ENDDATE, ISVALID from TQ_OA_SECURITYMAP where MAPTYPE in
    # (5,14,16,17,18,19,22,24,25,40,47,48,50,61,64,65,68,69,7,8,9) and SETYPE IN ('301','302') limit %s, 1000;''')

    TABLE = 'fund_code'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'begin_date':False, 'map_type':True, 'map_code':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键

    DATETIME = {
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = FundCodeMap
    MAP_VALUES = FundCodeValueMap


class FundCodeFromSecBasic(DataTableTransfer):
    """
    中心库表之间互导
    证券代码对应表(sec_basic_info) --> 基金编码表(fund_code)
    """

    # 待插入的数据查询SQL
    QUERY_SQL = ('''select sec_uni_code, sec_code, '1914003' as map_type, case when security_type=1003002 
    then concat(sec_code, '.SH')  when security_type=1003001 then concat(sec_code, '.SZ') else '' end as abc_code, 
    ipo_date, end_date from sec_basic_info where sec_type='1004003' AND sec_small_type 
    IN('1004003002','1004003003') AND ipo_status IN('1002007','1002006','1002003') limit %s, 1000;''')

    QUERY_SQL_INCREASE = ('''select sec_uni_code, sec_code, '1914003' as map_type, case when security_type=1003002 
    then concat(sec_code, '.SH')  when security_type=1003001 then concat(sec_code, '.SZ') else '' end as abc_code, 
    ipo_date, end_date, '1' from sec_basic_info where sec_type='1004003' AND sec_small_type 
    IN('1004003002','1004003003') AND ipo_status IN('1002007','1002006','1002003') 
    and updatetime > date_sub(now(), interval 1 day) limit %s, 1000;''')

    STATS_SQL = ('''select count(1) from sec_basic_info where sec_type='1004003' AND sec_small_type 
    IN('1004003002','1004003003') AND ipo_status IN('1002007','1002006','1002003');''')  # 统计SQL
    STATS_SQL_INCREASE = ('''select count(1) from sec_basic_info where sec_type='1004003' AND sec_small_type 
    IN('1004003002','1004003003') AND ipo_status IN('1002007','1002006','1002003') 
    and updatetime > date_sub(now(), interval 1 day);''')  # 统计SQL
    TABLE = 'fund_code'  # 待插入的表名
    SOURCE_ID = ('sec_uni_code', 'begin_date', 'map_type', 'map_code')  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    funds = configs['mysql']
    MAP_FIELDS = FundCodeFromSecBasicMap


class IntermediaryOrg(DataTableTransfer):
    """
    基金当事人(TQ_FD_PARTY) --> 中介机构表(intermediary_org)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code, BEGINDATE as begin_date, ENDDATE as end_date, PARTYTYPE as org_type,
     COMPCODE as org_uni_code,COMPNAME as org_name  from TQ_FD_PARTY where PARTYTYPE in 
     (1,2,3,4,5,6,7,8,11,12,13,14,15,16,18,19,20,21,22,23,24,25,26,27,28,29,30,31);'''

    QUERY_TARGET = '''select replace(s.source_id,'FD','') as sec_uni_code,replace(begin_date,'-','') as begin_date,
    replace(i.end_date,'-','') as end_date,i.org_type,replace(o.source_id,'FD','') as org_uni_code,i.org_name
    from intermediary_org i join sec_basic_info s on i.sec_uni_code = s.sec_uni_code join
      org_basic_info o on i.org_uni_code = o.com_uni_code where  i.status<>'9' ;'''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD',SECURITYID) as SECURITYID, PUBLISHDATE, BEGINDATE, ENDDATE, PARTYTYPE, COMPNAME,
    #  concat('FD', COMPCODE) as COMPCODE, ISVALID from TQ_FD_PARTY where PARTYTYPE in
    #  (1,2,3,4,5,6,7,8,11,12,13,14,15,16,18,19,20,21,22,23,24,25,26,27,28,29,30,31) limit %s, 1000;''')

    TABLE = 'intermediary_org'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'begin_date':False, 'end_date':False, 'org_type':True, 'org_uni_code':False, 'org_name':False}
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键

    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = IntermediaryOrgMap
    MAP_VALUES = IntermediaryOrgValueMap


class ETFSubRedemList(DataTableTransfer):
    """
    ETF申赎概况汇总表(TQ_FD_ETFSRSUMM)	 --> ETF申购赎回清单信息(fund_etf_sub_redem_list)
    """
    QUERY_SOURCE = '''select  SECURITYID as sec_uni_code,TRADEDATE as  trade_date,SUBCOMPLIST as sub_redem_sec_type
    from TQ_FD_ETFSRSUMM ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as sec_uni_code,replace(trade_date,'-','') as trade_date,sub_redem_sec_type
    from fund_etf_sub_redem_list f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' '''
    # 待插入的数据查询SQL
    # QUERY_SQL = ('''select concat('FD', SECURITYID) SECURITYID, PUBLISHDATE,TRADEDATE,SUBCOMPLIST,SYMBOL,CASHBALANCE,
    # UNITMINSUBNAV,NAVSHARE,ESTIMATEDCASH,MAXCASHRTO,UNITMINSUB,ISPUBIOPV,TDACCSUBLIMIT,TDACCREDLIMIT,OATDACCSUBLIMIT,
    # OATDACCREDLIMIT,ISALLOWSUB,ISALLOWRED from TQ_FD_ETFSRSUMM limit %s, 1000;''')

    TABLE = 'fund_etf_sub_redem_list'  # 待插入的表名
    SOURCE_ID = {'sec_uni_code':False, 'trade_date':False, 'sub_redem_sec_type':True}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键

    DATETIME = {
        'announcement_date': None,
        'trade_date': None,
    }

    MAP_FIELDS = ETFSubRedemListMap
    MAP_VALUES = ETFSubRedemListValueMap


class ETFSRComponents(DataTableTransfer):
    """
    ETF申赎成份券汇总表(TQ_FD_ETFSRCOMPSUMM) --> ETF申购赎回成份信息(fund_etf_sub_redem_components)
    """
    QUERY_SOURCE = '''select  SECURITYID as fund_uni_code,TRADEDATE as  trade_date,SUBCOMPLIST as sub_redem_sec_type,
    CSYMBOL as  sec_code  from TQ_FD_ETFSRCOMPSUMM ;'''

    QUERY_TARGET = '''select replace(b.source_id,'FD','') as fund_uni_code,replace(trade_date,'-','') as trade_date,sub_redem_sec_type,
     sec_code  from fund_etf_sub_redem_components f join sec_basic_info b on f.sec_uni_code = b.sec_uni_code where  f.status<>'9' '''
    # 待插入的数据查询SQL

    # QUERY_SQL = ('''select concat('FD', SECURITYID) SECURITYID,DECLAREDATE,TRADEDATE,SUBCOMPLIST,SYMBOL,CSYMBOL,SNAME,
    # STKQUT,CASHREPLACEFLAG,CASHREPLACERTO,REPLACEAMT from TQ_FD_ETFSRCOMPSUMM limit %s, 10000;''')

    TABLE = 'fund_etf_sub_redem_components'  # 待插入的表名
    SOURCE_ID = {'fund_uni_code':False, 'trade_date':False, 'sub_redem_sec_type':True, 'sec_code':False}  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键

    DATETIME = {
        'announcement_date': None,
        'trade_date': None,
    }

    MAP_FIELDS = ETFSRComponentsMap
    MAP_VALUES = ETFSRComponentsValueMap
    IDX = ('fund_uni_code', 'sub_redem_sec_type', 'sec_code')

    @classmethod
    def work_batch(cls, start=0, end=25000000):
        """
        work（分批次work）
        :param start:
        :param end:
        :return:
        """

        try:
            if len(sys.argv) >= 3:
                start = int(sys.argv[1])
                end = int(sys.argv[2])
        except Exception as ex:
            logging.error('Input args error: {0}'.format(ex))

        logging.info('The data transfer full is begin, table: {0}'.format(cls.TABLE))
        for index in xrange(start, end, 10000):
            sql = cls.QUERY_SQL % index
            rows = cls.export_mysql(sql)
            logging.info('The table {1} records is {0} items, export finished.'.format(index+10000, cls.TABLE))
            cls.upset_mysql(cls.TABLE, rows)
            logging.info('The table {1} records is {0} items, update finished.'.format(index+10000, cls.TABLE))

        logging.info('The data transfer full is end.')

    @classmethod
    def import_mysql_data(cls, table, query, flag):
        """
        导表(千万级数据，每次按照万级输出)
        :param table:
        :param query:
        :param flag:
        :return:
        """

        total = cls.get_total_number(flag)
        logging.info('The table {1} total records is {0} items, {2} running...'.format(total, table, flag))
        for index in xrange(0, total, 10000):
            sql = query % index
            rows = cls.export_mysql(sql)
            logging.info('The table {1} records is {0} items, export finished.'.format(index+10000, table))
            cls.upset_mysql(table, rows)
            logging.info('The table {1} records is {0} items, update finished.'.format(index+10000, table))

    @classmethod
    def write_mysql(cls, table, item, column_value, upset):
        """
        更新数据写入MySQL
        :param table:
        :param item:
        :param column_value:
        :param upset:
        :return:
        """

        # 不能存sec_uni_code=0的记录
        if item.has_key('fund_uni_code') and item['fund_uni_code'] == 0:
            return

        # 修正sec_uni_code的值
        query_stock = '''select sec_uni_code from sec_basic_info where sec_type=1004001 and sec_code=%s limit 1;'''
        query_bonds = '''select sec_uni_code from sec_basic_info where sec_type=1004002 and sec_code=%s limit 1;'''
        if item['sub_redem_sec_type'] == u'股票':
            item['sec_uni_code'] = cls.exec_sql(conf='data_center', sql=query_stock, params=(item['sec_code'], ))
        elif item['sub_redem_sec_type'] == u'债券':
            item['sec_uni_code'] = cls.exec_sql(conf='data_center', sql=query_bonds, params=(item['sec_code'], ))
        else:
            item['sec_uni_code'] = None

        conn = MysqlUtils.connect(cls.data_center)
        if upset and column_value:
            cursor = MysqlUtils.columns_update(conn=conn, table=table,
                                               columns=tuple(item.keys()), values=tuple(item.values()),
                                               select_column=cls.COLUMN_ID, select_value=column_value)
            # logging.info('The record {0} {1} update finished.'.format(item['fund_uni_code'], item['sec_code']))
        else:
            cursor = MysqlUtils.columns_insert(conn=conn, table=table,
                                               columns=tuple(item.keys()), values=tuple(item.values()))
            # logging.info('The record {0} {1} insert finished.'.format(item['fund_uni_code'], item['sec_code']))
        if cursor:
            cursor.close()
        conn.close()


class StructFundBasic(DataTableTransfer):
    """
    分级基金概况(TQ_FD_CLASSINFO) --> 分级基金基本资料(structured_fund_basic_info)
    """

    # 待插入的数据查询SQL
    QUERY_SQL = ('''select concat('FD', SECURITYID) as SECURITYID,SESNAME,concat('FD', SECURITYIDCOMP) SECURITYIDCOMP,
    SHARETYPE,ISCONVERTED, LEVERAGEMAX,MACTHRATIO,CLOSPERIOD,CLOSPERIODUNIT,AFCLSTYPE,AFOPRTYPE,ISLOF,ISREGULARCVT,
    CONVERTBASEDATE,CONVERTTYPE,REGCVTCYCLE,REGCVTCUNIT,NOCONVERT,ISUPCONVERT,UPCONVERTTYPE,ISDWCONVERT,DWCONVERTTYPE,
    ISSTABLE,ISMASTERRED from TQ_FD_CLASSINFO limit %s, 1000;''')

    QUERY_SQL_INCREASE = ('''select concat('FD', SECURITYID) as SECURITYID,SESNAME,
    concat('FD', SECURITYIDCOMP) SECURITYIDCOMP,SHARETYPE,ISCONVERTED,LEVERAGEMAX,MACTHRATIO,CLOSPERIOD,
    CLOSPERIODUNIT,AFCLSTYPE,AFOPRTYPE,ISLOF,ISREGULARCVT,CONVERTBASEDATE CONVERTTYPE,REGCVTCYCLE,REGCVTCUNIT,
    NOCONVERT,ISUPCONVERT,UPCONVERTTYPE,ISDWCONVERT,DWCONVERTTYPE,ISSTABLE,ISMASTERRED 
    from TQ_FD_CLASSINFO where ENTRYDATE > date_sub(now(), interval 1 day) limit %s, 1000;''')

    STATS_SQL = '''select count(1) from TQ_FD_CLASSINFO;'''  # 统计SQL
    STATS_SQL_INCREASE = ('''select count(1) from TQ_FD_CLASSINFO 
    where ENTRYDATE > date_sub(now(), interval 1 day);''')  # 统计SQL
    TABLE = 'structured_fund_basic_info'  # 待插入的表名
    SOURCE_ID = ('sec_uni_code', )   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'sec_uni_code'  # 更新主键

    MAP_FIELDS = StructFundBasicMap
    MAP_VALUES = StructFundBasicValueMap


class StructFundPreReturn(DataTableTransfer):
    """
    分级基金约定收益率(TQ_FD_CLASSARRETURN) --> 分级基金约定收益(structured_fund_predicted_return)
    """

    # 待插入的数据查询SQL
    QUERY_SQL = ('''select concat('FD', SECURITYID) as SECURITYID,DECLAREDATE,BEGINDATE,ENDDATE,ITYPE,CALTYPE,
    CHANGEFREP,ELDMEMO,ISTAXED,BMKNAME,BENCHMARK,MULTIPLE,IMARGIN,MEMO 
    from TQ_FD_CLASSARRETURN limit %s, 1000;''')

    QUERY_SQL_INCREASE = ('''select concat('FD', SECURITYID) as SECURITYID,DECLAREDATE,BEGINDATE,ENDDATE,ITYPE,
    CALTYPE,CHANGEFREP,ELDMEMO,ISTAXED,BMKNAME,BENCHMARK,MULTIPLE,IMARGIN,MEMO from TQ_FD_CLASSARRETURN 
    where ENTRYDATE > date_sub(now(), interval 1 day) limit %s, 1000;''')

    STATS_SQL = '''select count(1) from TQ_FD_CLASSARRETURN;'''  # 统计SQL
    STATS_SQL_INCREASE = ('''select count(1) from TQ_FD_CLASSARRETURN  
    where ENTRYDATE > date_sub(now(), interval 1 day);''')  # 统计SQL
    TABLE = 'structured_fund_predicted_return'  # 待插入的表名
    SOURCE_ID = ('sec_uni_code', 'begin_date')  # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = StructFundPreReturnMap
    MAP_VALUES = StructFundPreReturnValueMap


class StructFundShareMatch(DataTableTransfer):
    """
    分级基金配比(TQ_FD_CLASSRATIO) --> 分级基金份额配比(structured_fund_share_matching)
    """

    # 待插入的数据查询SQL
    QUERY_SQL = ('''select concat('FD', SECURITYID) as SECURITYID,UPDATEDATE,BEGINDATE,ENDDATE,
    concat('FD', SECURITYIDA)  SECURITYIDA,concat('FD', SECURITYIDB) SECURITYIDB,
    MASTERRATIO,AFUNDRATION,BFUNDRATIO from TQ_FD_CLASSRATIO limit %s, 1000;''')

    QUERY_SQL_INCREASE = ('''select concat('FD', SECURITYID) as SECURITYID,UPDATEDATE,BEGINDATE,ENDDATE,
    concat('FD', SECURITYIDA)  SECURITYIDA,concat('FD', SECURITYIDB) SECURITYIDB,
    MASTERRATIO,AFUNDRATION,BFUNDRATIO from TQ_FD_CLASSRATIO
    where ENTRYDATE > date_sub(now(), interval 1 day) limit %s, 1000;''')

    STATS_SQL = '''select count(1) from TQ_FD_CLASSRATIO;'''  # 统计SQL
    STATS_SQL_INCREASE = ('''select count(1) from TQ_FD_CLASSRATIO   
    where ENTRYDATE > date_sub(now(), interval 1 day);''')  # 统计SQL
    TABLE = 'structured_fund_share_matching'  # 待插入的表名
    SOURCE_ID = ('sec_uni_code', 'begin_date')   # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = StructFundShareMatchMap
    MAP_VALUES = StructFundShareMatchValueMap


class StructFundThreshold(DataTableTransfer):
    """
    分级基金阀值表(TQ_FD_CLASSTHRESHOLD) --> 分级基金阈值(structured_fund_threshold)
    """

    # 待插入的数据查询SQL
    QUERY_SQL = ('''select concat('FD', SECURITYID) as SECURITYID,UPDATEDATE,BEGINDATE,ENDDATE,CUR,RELATIONTYPE,
    RELATIONCOND,THRESHOLD,ISCONVERTED,concat('FD', MULSECURITYID) MULSECURITYID,MEMO 
    from TQ_FD_CLASSTHRESHOLD limit %s, 1000;''')

    QUERY_SQL_INCREASE = ('''select concat('FD', SECURITYID) as SECURITYID,UPDATEDATE,BEGINDATE,ENDDATE,CUR,
    RELATIONTYPE,RELATIONCOND,THRESHOLD,ISCONVERTED,concat('FD', MULSECURITYID) MULSECURITYID,MEMO 
    from TQ_FD_CLASSTHRESHOLD where ENTRYDATE > date_sub(now(), interval 1 day) limit %s, 1000;''')

    STATS_SQL = '''select count(1) from TQ_FD_CLASSTHRESHOLD;'''  # 统计SQL
    STATS_SQL_INCREASE = ('''select count(1) from TQ_FD_CLASSTHRESHOLD    
    where ENTRYDATE > date_sub(now(), interval 1 day);''')  # 统计SQL
    TABLE = 'structured_fund_threshold'  # 待插入的表名
    SOURCE_ID = ('sec_uni_code', 'begin_date', 'relationship_type', 'conditional_relationship')
    # 被关联表的主键, 源表与目标表的关联字段
    COLUMN_ID = 'id'  # 更新主键
    DATETIME = {
        'announcement_date': None,
        'begin_date': None,
        'end_date': None,
    }

    MAP_FIELDS = StructFundThresholdMap
    MAP_VALUES = StructFundThresholdValueMap


if __name__ == '__main__':

    log_path = 'export_fund_info2_server.log'
    logging.basicConfig(filename=log_path,
                        format='%(asctime)s %(name)s %(levelname)s [line %(lineno)s]: %(message)s',
                        filemode='w',
                        level=logging.INFO)

    # PeopleBasicInfo.work()
    # FundManagerBasicInfo.work()
    # InstitutionsChange.work()
    # SecuritiesChange.work()
    # FundComHolder.work()
    # FundCode.work()
    # FundCodeFromSecBasic.work()
    # IntermediaryOrg.work()
    # ETFSubRedemList.work()
    ETFSRComponents.work()
    # StructFundBasic.work()
    # StructFundPreReturn.work()
    # StructFundShareMatch.work()
    # StructFundThreshold.work()
